import pymysql
import os

from config.config import *

def connect_mysql():
    db_config = {
        "host": MYSQL_HOST,
        "user": MYSQL_USER,
        "password": MYSQL_PASSWORD,
        "database": MYSQL_DATABASE,
        "charset": CHARSET
    }
    # 建立连接
    conn = pymysql.connect(**db_config,client_flag=pymysql.constants.CLIENT.MULTI_STATEMENTS)
    return conn

def execute_stored_proc(sql_file_path):
    if not os.path.exists(sql_file_path):
        print(f"文件不存在: {sql_file_path}")
        return

    # 读取SQL文件内容
    with open(sql_file_path, 'r', encoding='utf-8') as f:
        sql_content = f.read()

    # 连接数据库（关键：添加 client_flag 参数）
    # conn = pymysql.connect(
    #     **db_config,
    #     client_flag=pymysql.constants.CLIENT.MULTI_STATEMENTS  # 允许执行多语句
    # )
    conn=connect_mysql()
    cursor = conn.cursor()

    try:
        # 执行SQL（包含删除和创建存储过程）
        cursor.execute(sql_content)
        conn.commit()
        print("存储过程执行成功")
    except pymysql.MySQLError as e:
        print(f"执行失败: {e}")
        conn.rollback()
    finally:
        cursor.close()
        conn.close()


def call_stored_procedure(proc_name, params=()):
    """
    调用MySQL中的存储过程并返回结果

    :param proc_name: 存储过程名称
    :param params: 存储过程所需的参数（元组形式）
    :return: 存储过程返回的结果集列表
    """
    conn = connect_mysql()
    cursor = conn.cursor(pymysql.cursors.DictCursor)

    try:
        # 调用存储过程
        cursor.callproc(proc_name, params)

        # 关键修复：提交事务（确保INSERT/UPDATE/DELETE操作生效）
        conn.commit()

        # 获取结果集
        results = []
        while True:
            result = cursor.fetchall()
            if result:
                results.append(result)
            if not cursor.nextset():
                break

        print(f"存储过程 {proc_name} 调用成功并提交事务")
        return results

    except pymysql.MySQLError as e:
        print(f"调用存储过程失败: {e}")
        conn.rollback()  # 出错时回滚事务
        return None

    finally:
        cursor.close()
        conn.close()


# if __name__ == "__main__":
#     # 数据库配置
#     db_config = {
#         "host": MYSQL_HOST,
#         "user": MYSQL_USER,
#         "password": MYSQL_PASSWORD,
#         "database": MYSQL_DATABASE,
#         "charset": CHARSET
#     }

    # SQL文件路径（根据你的实际路径修改）
# sql_file = os.path.join(os.path.dirname(__file__), '../sql', 'CreateWarehousesAndAreas')
# execute_stored_proc(sql_file)
# call_stored_procedure('CreateWarehousesAndAreas')




